package com.eascs.web.o2o.web.service.impl;

import com.eascs.app.context.ConfigContext;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.dao.sql.ObjectSqlBuilder;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.site.view.BaseRestResult;
import com.eascs.web.o2o.web.controller.OverdualReportRestController;
import com.eascs.web.o2o.web.dao.OverdueReportDao;
import com.eascs.web.o2o.web.dao.WebAdminDao;
import com.eascs.web.o2o.web.dao.WebBaseDao;
import com.eascs.web.o2o.web.dao.BiodsDao;
import com.eascs.web.o2o.web.entity.BasicInformationOverdue;
import com.eascs.web.o2o.web.entity.ImportReportOverdueData;
import com.eascs.web.o2o.web.entity.LoanBaseInfo;
import com.eascs.web.o2o.web.form.OverdualForm;
import com.eascs.web.o2o.web.form.OverdualStatForm;
import com.eascs.web.o2o.web.form.ReportStatForm;
import com.eascs.web.o2o.web.service.OverdualReportService;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class OverdualReportServiceImpl implements OverdualReportService {
	
	private static Logger log = LoggerFactory.getLogger(OverdualReportRestController.class);

	@Autowired
	WebBaseDao webBaseDao;
	
	@Autowired
	WebAdminDao webAdminDao;

	@Autowired
	OverdueReportDao overdueReportDao;
	
	@Autowired
	BiodsDao biodsDao;

	@Override
	public PageResult<Object[]> getOverdualReportByPage(OverdualForm entity) {

		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(),
				entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		if (entity.getType() != 1) {
			querySql.append(
					"SELECT overdue.billcode, refund.ltd_name, refund.loan_name, refund.loan_bank, refund.loan_amount, DATE_FORMAT(base.end_bank_date,'%Y-%m-%d') as end_bank_date, overdue. STATUS, DATE_FORMAT(disposal_date,'%Y-%m-%d')  AS disposal_date,  default_amout,  overdue_principal,  overdue_interest,   penalty_interest,  disposal_amount, DATE_FORMAT(statistical_time,'%Y-%m-%d')  AS statistical_time  FROM basic_information_overdue overdue LEFT JOIN report_refund_add refund ON overdue.billcode = refund.bill_code LEFT JOIN loan_base_info base ON overdue.billcode = base.bill_code  left join o2o_platform_regional  regional  on  regional.platform_id=refund.ltd WHERE /**DATA_PERMISSION(base.operator_agency)**/ is_new = 'Y' ");
		} else {
			querySql.append(
					"SELECT overdue.billcode, base.operator_name, refund.ltd_name, refund.province, refund.loan_name, refund.loan_bank, refund.loan_amount, base.loan_time, base.end_bank_date, CASE WHEN overdue. STATUS = 1 THEN '利息逾期 ' WHEN overdue. STATUS = 2 THEN '本金逾期' WHEN overdue. STATUS = 3 THEN '代偿' WHEN overdue. STATUS = 4 THEN '利息结清' WHEN overdue. STATUS = 5 THEN '本金结清' END AS STATUS, disposal_date AS disposal_date,  default_amout,   overdue_principal,  overdue_interest,  penalty_interest,  disposal_amount, DATE_FORMAT(statistical_time,'%Y-%m-%d')  AS statistical_time FROM basic_information_overdue overdue LEFT JOIN report_refund_add refund ON overdue.billcode = refund.bill_code LEFT JOIN loan_base_info base ON overdue.billcode = base.bill_code left join o2o_platform_regional  regional  on  regional.platform_id=refund.ltd  WHERE /**DATA_PERMISSION(base.operator_agency)**/ is_new = 'Y'");
		}
		if (!StringUtil.isEmpty(entity.getBillCode())) {
			querySql.append(" AND base.BILL_CODE LIKE ? ");
			sqlPageQuery.addLikeParam(entity.getBillCode());
		}

		if (!StringUtil.isEmpty(entity.getRefundBank())) {
			querySql.append(" AND base.BANKREFUND = ? ");
			sqlPageQuery.addParam(entity.getRefundBank());
		}

		if (!StringUtil.isEmpty(entity.getStatus())) {
			querySql.append(" AND overdue.`STATUS` = ? ");
			sqlPageQuery.addParam(entity.getStatus());
		}

		if (!StringUtil.isEmpty(entity.getProvince())) {
			querySql.append(" AND regional.regional_name = ? ");
			sqlPageQuery.addParam(entity.getProvince());
		}
		if (!StringUtil.isEmpty(entity.getLoanName())) {
			querySql.append(" AND refund.LOAN_NAME LIKE ? ");
			sqlPageQuery.addLikeParam(entity.getLoanName());
		}

		if (!StringUtil.isEmpty(entity.getLtd())) {
			querySql.append(" AND base.OPERATOR_AGENCY LIKE ? ");
			sqlPageQuery.addLikeParam(entity.getLtd());
		}
		querySql.append(" ORDER BY base.end_bank_date desc,convert(refund.loan_name using gbk) asc ");
		sqlPageQuery.setSumColumnIndexs(new int[] { 4, 8, 9, 10, 11, 12 });

		sqlPageQuery.setQuerySql(querySql.toString());
		return webAdminDao.findDataWithPage(sqlPageQuery);
	}

	// 根据平台查询逾期报表
	@Override
	public PageResult<Object[]> getOverdualReportStatByPlatformByPage(OverdualStatForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		ObjectSqlBuilder objectSqlBuilder = ObjectSqlBuilder.create(entity, "");
		sqlPageQuery.setQuerySql(ConfigContext.getStringValue("sql.OverdualReportServiceImpl.OverdualPlatform")+objectSqlBuilder.getSql()+" GROUP BY ol.ltd ORDER BY CONVERT (regional_name USING gbk) ASC, ltdname ");
		sqlPageQuery.addParams(objectSqlBuilder.getParams());
		sqlPageQuery.setSumColumnIndexs(new int[] { 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 });
		return this.biodsDao.findDataWithPage(sqlPageQuery);
	}

	// 根据省区查询逾期报表
	@Override
	public PageResult<Object[]> getOverdualReportStatByProvinceByPage(OverdualStatForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		ObjectSqlBuilder objectSqlBuilder = ObjectSqlBuilder.create(entity, "");
		sqlPageQuery.setQuerySql(ConfigContext.getStringValue("sql.OverdualReportServiceImpl.OverdualProvince")+objectSqlBuilder.getSql()+" GROUP BY regional_name ORDER BY CONVERT (regional_name USING gbk) ASC ");
		sqlPageQuery.addParams(objectSqlBuilder.getParams());
		sqlPageQuery.setSumColumnIndexs(new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 });
		return this.biodsDao.findDataWithPage(sqlPageQuery);
	}

	@Override
	public BaseRestResult<String> addList(List<List<Object>> result) {
		BaseRestResult<String> results = BaseRestResult.info("导入成功").andRefresh();
		User user = UserHolder.getUser();
		
	
		List<Integer> dateList = new ArrayList<Integer>();
		for (int i = 1; i < result.size(); i++) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date date = (Date)result.get(0).get(10);
			String firstDate = sdf.format(date);
			@SuppressWarnings({ "unchecked", "rawtypes" })
			List<Object> list = (List) result.get(i);
			Date datei = (Date)list.get(10);
			String otherDate = sdf.format(datei);
			if(!firstDate.equals(otherDate)){
				dateList.add(i+1);
			}
		}
		
		StringBuffer error = new StringBuffer();
		if(!dateList.isEmpty()){
			for(int i = 0;i < dateList.size(); i++){
				log.error("第"+dateList.get(i)+"行统计时间不一致！");
				error.append("第"+dateList.get(i)+"行统计时间不一致！");
			}
			results = BaseRestResult.error(error.toString());
		}else{
			//删除逾期表中的数据
			log.info("开始清除逾期表数据......");
			try {
				String deleteSql="DELETE FROM basic_information_overdue " ;
				webBaseDao.excuteProReport(deleteSql);
			} catch (Exception e) {
				e.printStackTrace();
				log.error("Error：清除逾期表数据失败！！！");
				throw e;
			}
			log.info("清除逾期表数据结束......");
			for (int i = 0; i < result.size(); i++) {
				// 插入数据
				@SuppressWarnings("unused")
				String rows = String.valueOf(i + 1);
				@SuppressWarnings({ "unchecked", "rawtypes" })
				List<Object> list = (List) result.get(i);
				String code = list.get(0).toString().trim(); // 贷款申请编号
				BasicInformationOverdue overdueReportPO = new BasicInformationOverdue();
				ImportReportOverdueData oldOverdueReportPO = new ImportReportOverdueData();

				// 新数据
				LoanBaseInfo apply = new LoanBaseInfo();
				// 主借贷表 apply
				apply = fingByBillCode(code.trim());
				// if (apply == null) {
				// log.error("单据号为:" + code + "的单不存在！");
				// continue;
				// }

				// 录入数据：
				overdueReportPO.setBillCode(apply.getBillCode());
				if ("3".equals(list.get(7).toString().split("-")[0])) {
					overdueReportPO.setCompensatoryAmount((BigDecimal) list.get(8));
				}
				if (user != null) {
					overdueReportPO.setOpPeople(user.getCname());
				}
				overdueReportPO.setCrmotoloanername((String) list.get(1));
				overdueReportPO.setDefaultAmout((BigDecimal) list.get(2)); // 拖欠总额
				overdueReportPO.setOverduePrincipal((BigDecimal) list.get(3)); // 逾期本金
				overdueReportPO.setOverdueInterest((BigDecimal) list.get(4));// 逾期利息
				overdueReportPO.setPenaltyInterest((BigDecimal) list.get(5));// 罚息合计
				overdueReportPO.setOverduePrincipalDays((String) list.get(6));// 逾期天数
				overdueReportPO.setStatus(list.get(7).toString().split("-")[0]); // 当前处置状态
				overdueReportPO.setDisposalAmount((BigDecimal) list.get(8)); // 处置金额
				overdueReportPO.setOpTime(new Date(System.currentTimeMillis()));// 操作时间
				overdueReportPO.setIsNew("Y");// 是否是最新的
				// overdueReportPO.setDisposalDate(com.eabiroom.utils.DateUtils.getFormatDate(list.get(9),"yyyy-MM-dd"));
				// 处置时间
				overdueReportPO.setDisposalDate((Date) list.get(9));
				overdueReportPO.setStatisticalTime((Date)list.get(10));

				// 将录入数据保存到历史表：
				oldOverdueReportPO.setBillCode(apply.getBillCode());
				if ("3".equals(list.get(7).toString().split("-")[0])) {
					oldOverdueReportPO.setCompensatoryAmount((BigDecimal) list.get(8));
				}
				if (user != null) {
					oldOverdueReportPO.setOpPeople(user.getCname());
				}
				oldOverdueReportPO.setCrmotoloanername((String) list.get(1));
				oldOverdueReportPO.setDefaultAmout((BigDecimal) list.get(2)); // 拖欠总额
				oldOverdueReportPO.setOverduePrincipal((BigDecimal) list.get(3)); // 逾期本金
				oldOverdueReportPO.setOverdueInterest((BigDecimal) list.get(4));// 逾期利息
				oldOverdueReportPO.setPenaltyInterest((BigDecimal) list.get(5));// 罚息合计
				oldOverdueReportPO.setOverduePrincipalDays((String) list.get(6));// 逾期天数
				oldOverdueReportPO.setStatus(list.get(7).toString().split("-")[0]); // 当前处置状态
				oldOverdueReportPO.setDisposalAmount((BigDecimal) list.get(8)); // 处置金额
				oldOverdueReportPO.setOpTime(new Date(System.currentTimeMillis()));// 操作时间
				// oldOverdueReportPO.setDisposalDate(com.eabiroom.utils.DateUtils.getFormatDate(list.get(9),"yyyy-MM-dd"));
				// 处置时间
				oldOverdueReportPO.setDisposalDate((Date) list.get(9));
				oldOverdueReportPO.setStatisticalTime((Date)list.get(10));
				saveBasicInformationOverdue(overdueReportPO);
				saveImportReportOverdueData(oldOverdueReportPO);

			}
			
		}
		return results;
	}

	public List<BasicInformationOverdue> findOverdueReportList(BasicInformationOverdue basicInformationOverdue) {
		StringBuffer sql = new StringBuffer("select * from basic_information_overdue od where 1 = 1 ");
		List<Object> listParams = new ArrayList<Object>();

		if (basicInformationOverdue != null) {
			if (StringUtil.isNotBlank(basicInformationOverdue.getIsNew())) {
				sql.append(" and od.is_new = ? ");
				listParams.add(basicInformationOverdue.getIsNew());
			}
		}
		return overdueReportDao.findEntitys(sql.toString(), BasicInformationOverdue.class, listParams);
	}

	public int updateEntityById(BasicInformationOverdue basicInformationOverdue) {
		return overdueReportDao.updateById(basicInformationOverdue);
	}

	public LoanBaseInfo fingByBillCode(String billCode) {
		String sql = "SELECT * from loan_base_info  WHERE bill_code = ?";
		return overdueReportDao.findEntity(sql, LoanBaseInfo.class, billCode);
	}

	public int saveBasicInformationOverdue(BasicInformationOverdue basicInformationOverdue) {
		return overdueReportDao.insert(basicInformationOverdue);
	}

	public int saveImportReportOverdueData(ImportReportOverdueData importReportOverdueData) {
		return overdueReportDao.insert(importReportOverdueData);
	}
}
